﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using XiaoGang.Soft.XGWMS.MODEL;
using XiaoGang.Soft.XGWMS.DBUtility;
using XiaoGang.Soft.XGWMS.COM;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_M012_MEMBER
    {        

        /// <summary>
        /// GetUserList
        /// </summary>
        /// <param name="modMember"></param>
        /// <returns></returns>
        public ReturnValue GetUserList(MOD_M012_MEMBER modMember)
        {
            StringBuilder strWhere = new StringBuilder();
            if (modMember.USER_ID.Trim() != "")//编号
            {
                strWhere.Append(String.Format(" AND M012.USER_ID like '%{0}%'", modMember.USER_ID.Trim()));
            }
            if (modMember.USER_NO.Trim() != "")//卡号
            {
                strWhere.Append(String.Format(" AND M012.USER_NO like '%{0}%'", modMember.USER_NO.Trim()));
            }
            if (modMember.USER_NAME.Trim() != "")//姓名
            {
                strWhere.Append(String.Format(" AND M012.USER_NAME LIKE '%{0}%'", modMember.USER_NAME.Trim()));
            }
            if (modMember.USER_SEX.Trim() != "")//性别
            {
                strWhere.Append(String.Format(" AND M012.USER_SEX like '%{0}%'", modMember.USER_SEX.Trim()));
            }
            if (modMember.USER_PHONE1.Trim() != "")
            {
                strWhere.Append(String.Format(" AND (M012.USER_PHONE1 Like '%{0}%' OR M012.USER_PHONE2  Like '%{0}%')", modMember.USER_PHONE1.Trim()));
            }
            if (modMember.USER_ADDRESS1.Trim() != "")
            {
                strWhere.Append(String.Format(" AND (M012.USER_ADDRESS1 LIKE '%{0}%' Or M012.USER_ADDRESS2 LIKE '%{0}%')", modMember.USER_ADDRESS1.Trim()));
            }
            if (modMember.USER_EMAIL.Trim() != "")
            {
                strWhere.Append(" AND M012.USER_EMAIL like '%" + modMember.USER_EMAIL.Trim() + "%'");
            }
            if (modMember.USER_POST.Trim() != "")
            {
                strWhere.Append(" AND M012.USER_POST like '%" + modMember.USER_POST.Trim() + "%'");
            }            
            if (modMember.MEMO.Trim() != "")
            {
                strWhere.Append(" AND M012.MEMO LIKE '%" + modMember.MEMO.Trim() + "%'");
            }
            strWhere.Append(" Order By M012.USER_NO");
            return DBUtility.SqlHelper.Query(DAL_M012_MEMBER_SQL.GetUserList + strWhere.ToString());
        }

        public ReturnValue GetMemberrList(string MemberNo)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(MemberNo))
            {
                strWhere += " AND USER_NO = '"+ MemberNo +"'";
            }
            return DBUtility.SqlHelper.Query(DAL_M012_MEMBER_SQL.GetMemberrList.Replace("@@@",strWhere));
        }

        /// <summary>
        /// AddUser
        /// </summary>
        /// <param name="modMember"></param>
        /// <returns></returns>
        public ReturnValue AddMember(MOD_M012_MEMBER modMember)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
                new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ID", SqlDbType.Char,12),
                new SqlParameter("@USER_NO", SqlDbType.Char,12),
                new SqlParameter("@USER_NAME", SqlDbType.VarChar,256),
                new SqlParameter("@USER_SEX", SqlDbType.Char,1),
                new SqlParameter("@USER_BIRTHDAY", SqlDbType.Char,8),
                new SqlParameter("@USER_PHONE1", SqlDbType.Char,20),
                new SqlParameter("@USER_PHONE2", SqlDbType.Char,20),
                new SqlParameter("@USER_ADDRESS1", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ADDRESS2", SqlDbType.VarChar,256),
                new SqlParameter("@USER_EMAIL", SqlDbType.VarChar,50),
                new SqlParameter("@USER_POST", SqlDbType.Char,10),
                new SqlParameter("@USER_PHOTO", SqlDbType.Image),
                new SqlParameter("@MEMO", SqlDbType.VarChar,256)};
            parameters[0].Value = modMember.UPDATER_ID;
            parameters[1].Value = modMember.TERMINAL_CD;
            parameters[2].Value = modMember.USER_ID;
            parameters[3].Value = modMember.USER_NO;
            parameters[4].Value = modMember.USER_NAME;
            parameters[5].Value = modMember.USER_SEX;
            parameters[6].Value = modMember.USER_BIRTHDAY;
            parameters[7].Value = modMember.USER_PHONE1;
            parameters[8].Value = modMember.USER_PHONE2;
            parameters[9].Value = modMember.USER_ADDRESS1;
            parameters[10].Value = modMember.USER_ADDRESS2;
            parameters[11].Value = modMember.USER_EMAIL;
            parameters[12].Value = modMember.USER_POST;
            if (modMember.USER_PHOTO == null)
            {
                parameters[13].Value = DBNull.Value;
            }
            else
            {
                parameters[13].Value = modMember.USER_PHOTO;
            }
            parameters[14].Value = modMember.MEMO;
            return DBUtility.SqlHelper.ExecuteSql(DAL_M012_MEMBER_SQL.AddMember, parameters);
        }

        /// <summary>
        /// GetUserPhoto
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <returns></returns>
        public ReturnValue GetUserPhoto(string USER_ID)
        {
            SqlParameter[] parameters = { new SqlParameter("@USER_ID", SqlDbType.Char, 12) };
            parameters[0].Value = USER_ID;
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetUserPhoto, parameters);
        }

        /// <summary>
        /// GetMemberBuyDetail
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <returns></returns>
        public ReturnValue GetMemberBuyDetail(string USER_NO)
        {
            SqlParameter[] parameters = { new SqlParameter("@USER_NO", SqlDbType.VarChar, 12) };
            parameters[0].Value = USER_NO;
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetMemberBuyDetail, parameters);
        }

        /// <summary>
        /// GetSalesDetail
        /// </summary>
        /// <param name="strDateFrom"></param>
        /// <param name="strDateTo"></param>
        /// <returns></returns>
        public ReturnValue GetSalesDetail(string strDateFrom,string strDateTo)
        {
            string strWhere = string.Empty;
            if (strDateFrom.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME >='"+strDateFrom+"' ";
            }
            if (strDateTo.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME <='"+strDateTo+"'";
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetSalesDetail.Replace("@@@", strWhere));
        }

        /// <summary>
        /// GetBuyDetail
        /// </summary>
        /// <param name="strDateFrom"></param>
        /// <param name="strDateTo"></param>
        /// <returns></returns>
        public ReturnValue GetBuyDetail(string strDateFrom, string strDateTo)
        {
            string strWhere = string.Empty;
            if (strDateFrom.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME >='" + strDateFrom + "' ";
            }
            if (strDateTo.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME <='" + strDateTo + "'";
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetBuyDetail.Replace("@@@", strWhere));
        }

        /// <summary>
        /// 获取盘库明细
        /// </summary>
        /// <param name="strDateFrom"></param>
        /// <param name="strDateTo"></param>
        /// <returns></returns>
        public ReturnValue GetStocktakingDetail(string strDateFrom, string strDateTo)
        {
            string strWhere = string.Empty;
            if (strDateFrom.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME >='" + strDateFrom + "' ";
            }
            if (strDateTo.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME <='" + strDateTo + "'";
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetStocktakingDetail.Replace("@@@", strWhere));
        }

        /// <summary>
        /// GetBillTotalPrice
        /// </summary>
        /// <param name="strDateFrom"></param>
        /// <param name="strDateTo"></param>
        /// <returns></returns>
        public ReturnValue GetBillTotalPrice(string strDateFrom, string strDateTo)
        {
            string strWhere = string.Empty;
            if (strDateFrom.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME >='"+strDateFrom+"' ";
            }
            if (strDateTo.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME <='"+strDateTo+"'";
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetBillTotalPrice.Replace("@@@", strWhere));
        }

        /// <summary>
        /// GetBuyTotalPrice
        /// </summary>
        /// <param name="strDateFrom"></param>
        /// <param name="strDateTo"></param>
        /// <returns></returns>
        public ReturnValue GetBuyTotalPrice(string strDateFrom, string strDateTo)
        {
            string strWhere = string.Empty;
            if (strDateFrom.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME >='" + strDateFrom + "' ";
            }
            if (strDateTo.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME <='" + strDateTo + "'";
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetBuyTotailPrice.Replace("@@@", strWhere));
        }

        /// <summary>
        /// 盘库汇总
        /// </summary>
        /// <param name="strDateFrom"></param>
        /// <param name="strDateTo"></param>
        /// <returns></returns>
        public ReturnValue GetStocktakingTotal(string strDateFrom, string strDateTo)
        {
            string strWhere = string.Empty;
            if (strDateFrom.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME >='" + strDateFrom + "' ";
            }
            if (strDateTo.Trim().Length > 0)
            {
                strWhere += " And M010.BUSINESS_TIME <='" + strDateTo + "'";
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetStocktakingTotal.Replace("@@@", strWhere));
        }

        /// <summary>
        /// DelUser
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <returns></returns>
        public ReturnValue DelUser(string USER_ID)
        {
            SqlParameter[] parameters = { new SqlParameter("@USER_ID", SqlDbType.Char, 12) };
            parameters[0].Value = USER_ID;
            return SqlHelper.ExecuteSql(DAL_M012_MEMBER_SQL.DelUser, parameters);
        }

        public ReturnValue DelUser_Logic(MOD_M012_MEMBER modMember)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
                new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ID", SqlDbType.Char,12)};
            parameters[0].Value = modMember.UPDATER_ID;
            parameters[1].Value = modMember.TERMINAL_CD;
            parameters[2].Value = modMember.USER_ID;
            return DBUtility.SqlHelper.ExecuteSql (DAL_M012_MEMBER_SQL.DelUser_Logic, parameters);
        }

        /// <summary>
        /// 更新用户
        /// </summary>
        /// <param name="modMember"></param>
        /// <returns></returns>
        public ReturnValue UpdateMember(MOD_M012_MEMBER modMember)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
                new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ID", SqlDbType.Char,12),
                new SqlParameter("@USER_NO", SqlDbType.Char,12),
                new SqlParameter("@USER_NAME", SqlDbType.VarChar,256),
                new SqlParameter("@USER_SEX", SqlDbType.Char,1),
                new SqlParameter("@USER_BIRTHDAY", SqlDbType.Char,8),
                new SqlParameter("@USER_PHONE1", SqlDbType.Char,20),
                new SqlParameter("@USER_PHONE2", SqlDbType.Char,20),
                new SqlParameter("@USER_ADDRESS1", SqlDbType.VarChar,256),
                new SqlParameter("@USER_ADDRESS2", SqlDbType.VarChar,256),
                new SqlParameter("@USER_EMAIL", SqlDbType.VarChar,50),
                new SqlParameter("@USER_POST", SqlDbType.Char,10),
                new SqlParameter("@USER_PHOTO", SqlDbType.Image),
                new SqlParameter("@MEMO", SqlDbType.VarChar,256)};
            parameters[0].Value = modMember.UPDATER_ID;
            parameters[1].Value = modMember.TERMINAL_CD;
            parameters[2].Value = modMember.USER_ID;
            parameters[3].Value = modMember.USER_NO;
            parameters[4].Value = modMember.USER_NAME;
            parameters[5].Value = modMember.USER_SEX;
            parameters[6].Value = modMember.USER_BIRTHDAY;
            parameters[7].Value = modMember.USER_PHONE1;
            parameters[8].Value = modMember.USER_PHONE2;
            parameters[9].Value = modMember.USER_ADDRESS1;
            parameters[10].Value = modMember.USER_ADDRESS2;
            parameters[11].Value = modMember.USER_EMAIL;
            parameters[12].Value = modMember.USER_POST;
            if (modMember.USER_PHOTO == null)
            {
                parameters[13].Value = DBNull.Value;
            }
            else
            {
                parameters[13].Value = modMember.USER_PHOTO;
            }
            parameters[14].Value = modMember.MEMO;
            return DBUtility.SqlHelper.ExecuteSql(DAL_M012_MEMBER_SQL.UpdateMember, parameters);
        }

        /// <summary>
        /// 检查会员是否存在
        /// </summary>
        /// <param name="USER_ID"></param>
        /// <returns></returns>
        public ReturnValue CheckMemberExist(string MemberID)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(MemberID))
            {
                strWhere += String.Format(" and USER_ID = '{0}'", MemberID);
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetMemberID);
        }

        /// <summary>
        /// 检查非本ID但相同会员号的会员
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public ReturnValue CheckMemberNoWithoutID(string MemberID, string MemberNO)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(MemberID))
            {
                strWhere += String.Format(" and USER_ID <> '{0}'", MemberID);
            }

            if (!string.IsNullOrEmpty(MemberNO))
            {
                strWhere += String.Format(" and USER_NO = '{0}'", MemberNO);
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetMemberID + strWhere);
        }

        /// <summary>
        /// 检查会员号是否已经存在
        /// </summary>
        /// <param name="MemberNO"></param>
        /// <returns></returns>
        public ReturnValue CheckMemberNoExist(string MemberNO)
        {
            return CheckMemberNoWithoutID("", MemberNO);
        }

        public ReturnValue CheckUserNoExist(string User_NO,string User_ID)
        {
            SqlParameter[] parameters = { new SqlParameter ("@USER_NO", SqlDbType.Char, 12),
                new SqlParameter ("@USER_ID", SqlDbType.Char, 12)};
            parameters[0].Value = User_NO;
            parameters[1].Value = User_ID;
            return SqlHelper.Query (DAL_M012_MEMBER_SQL.CheckUserNoIDExist, parameters);
        }

        public ReturnValue CheckIsUseUserNo(string User_NO)
        {
            SqlParameter[] parameters = { new SqlParameter ("@USER_NO", SqlDbType.Char, 12) };
            parameters[0].Value = User_NO;
            return SqlHelper.Query (DAL_M012_MEMBER_SQL.CheckIsUseUserNo, parameters);
        }

        /// <summary>
        /// 取得正在使用的会员
        /// </summary>
        /// <param name="MemberID"></param>
        /// <returns></returns>
        public ReturnValue GetMemberUsed(string MemberID)
        {
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(MemberID))
            {
                strWhere += String.Format(" and USER_NO = '{0}'", MemberID);
            }
            return SqlHelper.Query(DAL_M012_MEMBER_SQL.GetMemberUsed + strWhere);
        }
    }
}
